﻿/*
Deployment script for Ruandao
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "Ruandao"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\"

GO
USE [master]

GO
:on error exit
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO

IF NOT EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'$(DatabaseName)')
BEGIN
    RAISERROR(N'You cannot deploy this update script to target COOLMAN-PC. The database for which this script was built, Ruandao, does not exist on this server.', 16, 127) WITH NOWAIT
    RETURN
END

GO

IF (@@servername != 'COOLMAN-PC')
BEGIN
    RAISERROR(N'The server name in the build script %s does not match the name of the target server %s. Verify whether your database project settings are correct and whether your build script is up to date.', 16, 127,N'COOLMAN-PC',@@servername) WITH NOWAIT
    RETURN
END

GO

IF CAST(DATABASEPROPERTY(N'$(DatabaseName)','IsReadOnly') as bit) = 1
BEGIN
    RAISERROR(N'You cannot deploy this update script because the database for which it was built, %s , is set to READ_ONLY.', 16, 127, N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO
USE [$(DatabaseName)]

GO
/*
 预先部署脚本模板							
--------------------------------------------------------------------------------------
 此文件包含将在生成脚本前执行的 SQL 语句	
 使用 SQLCMD 语法将文件包含在预先部署脚本中			
 示例:      :r .\filename.sql								
 使用 SQLCMD 语法引用预先部署脚本中的变量		
 示例:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/




GO
/*
-- 后期部署脚本模板							
----------------------------------------------------------------------------------------
-- 此文件包含将追加到生成脚本的 SQL 语句		
-- 使用 SQLCMD 语法将文件包含到后期部署脚本中			
-- 示例:      :r .\filename.sql								
-- 使用 SQLCMD 语法引用后期部署脚本中的变量		
-- 示例:      :setvar $TableName MyTable							
--               SELECT * FROM [$(TableName)]					
----------------------------------------------------------------------------------------

*/

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRolesView]'))
DROP VIEW [dbo].[UsersInRolesView]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UsersInRoles]') AND type in (N'U'))
DROP TABLE [dbo].[UsersInRoles]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RegisterUser]') AND type in (N'U'))
DROP TABLE [dbo].[RegisterUser]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserSetting]') AND type in (N'U'))
DROP TABLE [dbo].[UserSetting]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Setting]') AND type in (N'U'))
DROP TABLE [dbo].[Setting]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Role]') AND type in (N'U'))
DROP TABLE [dbo].[Role]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RoleCategory]') AND type in (N'U'))
DROP TABLE [dbo].[RoleCategory]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type in (N'U'))
DROP TABLE [dbo].[User]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountContext]') AND type in (N'U'))
DROP TABLE [dbo].[AccountContext]
GO

CREATE TABLE [dbo].[AccountContext]
(
	[AccountContextCode] [nvarchar] (16) NOT NULL
		constraint PK_AccountContext primary key,
	[EnablePasswordRetrieval] [bit] NOT NULL,
	[EnablePasswordReset] [bit] NOT NULL,
	[RequiresQuestionAndAnswer] [bit] NOT NULL,
	[RequiresUniqueEmail] [bit] NOT NULL,
	[PasswordFormat] [nvarchar] (64) NOT NULL,
	[MaxInvalidPasswordAttempts] [int] NOT NULL,
	[PasswordAttemptWindow] [int] NOT NULL,
	[MinRequiredPasswordLength] [int] NOT NULL,
	[MinRequiredPasswordSymbols] [int] NOT NULL,
	[PasswordStrengthRegularExpression] [nvarchar] (128) NULL,
	[Title] [nvarchar] (64) NOT NULL,
	[Description] [nvarchar] (256) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[RoleCategory]
(
	[RoleCategoryId] [int] identity(1,1) NOT NULL
		constraint PK_RoleCategory primary key,
	[Title] [nvarchar](64) NOT NULL,
	[Description] [nvarchar](256) NOT NULL,
	[AccountContextCode] [nvarchar](16) NOT NULL
		constraint FK_RoleCategory_AccountContext foreign key references AccountContext(AccountContextCode),
	constraint UIX_AccountContextCode_Tile unique (AccountContextCode, Title)
) ON [PRIMARY]

CREATE TABLE [dbo].[Role]
(
	[RoleId] int identity(1,1) NOT NULL
		constraint PK_Role primary key,
	[RoleCategoryId] int NOT NULL
		constraint FK_Role_RoleCategory foreign key references RoleCategory( RoleCategoryId ),
	[RoleName] [nvarchar] (64) NOT NULL,
	[LoweredRoleName] [nvarchar] (64) NOT NULL,
	[Description] [nvarchar] (256) NULL,
	constraint UIX_RoleCategoryId_LoweredRoleName unique (RoleCategoryId, LoweredRoleName )
) ON [PRIMARY]

CREATE TABLE [dbo].[User]
(
	[AccountContextCode] [nvarchar](16) NOT NULL
		constraint FK_User_AccountContext foreign key references AccountContext ( AccountContextCode ),
	[UserGuid] [uniqueidentifier] NOT NULL
		constraint PK_User primary key,
	[IsAnonymous] [bit] NOT NULL,
	[LastActivityDate] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[RegisterUser]
(
	[AccountContextCode] [nvarchar] (16) NOT NULL
		constraint FK_RegisterUser_AccountContext foreign key references AccountContext ( AccountContextCode ),
	[UserGuid] [uniqueidentifier] NOT NULL
		constraint PK_RegisterUser primary key
		constraint FK_RegisterUser_User foreign key references [User] (UserGuid),
	[UserName] [nvarchar] (64) NOT NULL,
	[LoweredUserName] [nvarchar] (64) NOT NULL,
	[NameAlias] [nvarchar] (64) NULL,
	[MobileAlias] [nvarchar] (64) NULL,
	[Password] [nvarchar] (128) NOT NULL,
	[PasswordFormat] [int] NOT NULL,
	[PasswordSalt] [nvarchar] (128) NOT NULL,
	[MobilePin] [nvarchar] (64) NULL,
	[Email] [nvarchar] (128) NULL,
	[LoweredEmail] [nvarchar] (128) NULL,
	[PasswordQuestion] [nvarchar] (128) NULL,
	[PasswordAnswer] [nvarchar] (128) NULL,
	[IsApproved] [bit] NOT NULL,
	[IsLockedOut] [bit] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[LastLoginDate] [datetime] NOT NULL,
	[LastPasswordChangedDate] [datetime] NOT NULL,
	[LastLockedOutDate] [datetime] NOT NULL,
	[FailedPasswordAttemptCount] [int] NOT NULL,
	[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
	[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
	[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
	[Comment] [nvarchar] (512) NULL,
	constraint UIX_AccountContextCode_LoweredEmail unique (AccountContextCode,LoweredEmail),
	constraint UIX_AccountContextCode_LoweredUserName unique (AccountContextCode,LoweredUserName)
) ON [PRIMARY]

CREATE TABLE [dbo].[UsersInRoles]
(
	[UserGuid] [uniqueidentifier] NOT NULL
		constraint FK_UsersInRoles_RegisterUser foreign key references RegisterUser (UserGuid),
	[RoleId] int NOT NULL
		constraint FK_UsersInRoles_Role foreign key references [Role] (RoleId),
	constraint PK_UsersInRoles primary key (UserGuid, RoleId)
) ON [PRIMARY]

CREATE TABLE [dbo].[Setting]
(
	SettingId int identity(1,1) NOT NULL
		constraint PK_Setting primary key,
	[SettingName] nvarchar(64) NOT NULL
		constraint UIX_SettingName unique,
	[TypeName] [nvarchar] (128) NOT NULL,
	[Data] [varbinary] (max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[UserSetting]
(
	UserGuid uniqueidentifier NOT NULL,
	SettingId int NOT NULL
		constraint FK_UserSetting_Setting foreign key references Setting (SettingId),
	Data [varbinary] (max) NOT NULL,
	constraint PK_UserSetting primary key (UserGuid, SettingId)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE VIEW dbo.UsersInRolesView
AS
SELECT     dbo.Role.RoleName, dbo.Role.LoweredRoleName, dbo.Role.RoleCategoryId, dbo.UsersInRoles.RoleId, dbo.UsersInRoles.UserGuid, 
                      dbo.RegisterUser.AccountContextCode, dbo.RegisterUser.LoweredUserName, dbo.RegisterUser.NameAlias, dbo.RegisterUser.LoweredEmail, 
                      dbo.RegisterUser.UserName, dbo.RegisterUser.Email, dbo.RoleCategory.Title, dbo.RoleCategory.[Description]
FROM         dbo.Role INNER JOIN
                      dbo.RoleCategory ON dbo.Role.RoleCategoryId = dbo.RoleCategory.RoleCategoryId INNER JOIN
                      dbo.UsersInRoles ON dbo.Role.RoleId = dbo.UsersInRoles.RoleId INNER JOIN
                      dbo.RegisterUser ON dbo.UsersInRoles.UserGuid = dbo.RegisterUser.UserGuid
GO


DELETE FROM  [UsersInRoles] 
 
DELETE FROM  [RegisterUser] 
 
DELETE FROM  [User] 

DELETE FROM  [Role]

DELETE FROM  [RoleCategory]

DELETE FROM  [AccountContext]
 
-- AccountContext
INSERT INTO [AccountContext] ([AccountContextCode] ,[EnablePasswordRetrieval] ,[EnablePasswordReset] ,[RequiresQuestionAndAnswer] ,[RequiresUniqueEmail] ,[PasswordFormat] ,[MaxInvalidPasswordAttempts] ,[PasswordAttemptWindow] ,[MinRequiredPasswordLength] ,[MinRequiredPasswordSymbols] ,[PasswordStrengthRegularExpression] ,[Title] ,[Description] ) VALUES ( 'Public',1,1,1,1,'Clear',5,5,6,0,Null,'���ڷ�����վ���˺�',Null )
INSERT INTO [AccountContext] ([AccountContextCode] ,[EnablePasswordRetrieval] ,[EnablePasswordReset] ,[RequiresQuestionAndAnswer] ,[RequiresUniqueEmail] ,[PasswordFormat] ,[MaxInvalidPasswordAttempts] ,[PasswordAttemptWindow] ,[MinRequiredPasswordLength] ,[MinRequiredPasswordSymbols] ,[PasswordStrengthRegularExpression] ,[Title] ,[Description] ) VALUES ( 'Internal',1,1,1,1,'Clear',5,5,6,0,Null,'�ڲ�������վ���˺�',Null )

-- RoleCategory
SET IDENTITY_INSERT [RoleCategory] ON 
INSERT INTO [RoleCategory] ([RoleCategoryId] ,[Title] ,[Description] ,[AccountContextCode] ) VALUES ( 1,'Common','ͨ�ý�ɫ���','Public' )
INSERT INTO [RoleCategory] ([RoleCategoryId] ,[Title] ,[Description] ,[AccountContextCode] ) VALUES ( 2,'Common','ͨ�ý�ɫ���','Internal' )
SET IDENTITY_INSERT [RoleCategory] OFF 

-- Role
SET IDENTITY_INSERT [Role] ON
INSERT INTO [Role] ([RoleId] ,[RoleCategoryId] ,[RoleName] ,[LoweredRoleName] ,[Description] ) VALUES ( 1,1,'User','user','��ͨ�û�' )
INSERT INTO [Role] ([RoleId] ,[RoleCategoryId] ,[RoleName] ,[LoweredRoleName] ,[Description] ) VALUES ( 2,1,'Admin','admin','��վ����Ա' )
INSERT INTO [Role] ([RoleId] ,[RoleCategoryId] ,[RoleName] ,[LoweredRoleName] ,[Description] ) VALUES ( 3,2,'User','user','��ͨ�û�' )
INSERT INTO [Role] ([RoleId] ,[RoleCategoryId] ,[RoleName] ,[LoweredRoleName] ,[Description] ) VALUES ( 4,2,'Admin','admin','��վ����Ա' )
SET IDENTITY_INSERT [Role] OFF

-- User
-- for Public - Common - User
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','1d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',0,'2010-12-28T14:36:39' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','1ac712c6-1ee3-4053-99b5-37949b3054c3',0,'2010-12-28T14:35:15' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','1964ec47-2ac7-4c33-9660-523fa9ebb799',0,'2010-12-28T14:25:43' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','1c84bf0a-b4f9-4059-99c0-669f649efefe',0,'2010-12-28T14:23:55' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','1b972aa7-04f9-4db9-90da-e0a878a5fc7a',0,'2010-12-28T14:24:40' )
-- for Public - Common - Admin
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','2d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',0,'2010-12-28T14:36:39' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','2ac712c6-1ee3-4053-99b5-37949b3054c3',0,'2010-12-28T14:35:15' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','2964ec47-2ac7-4c33-9660-523fa9ebb799',0,'2010-12-28T14:25:43' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','2c84bf0a-b4f9-4059-99c0-669f649efefe',0,'2010-12-28T14:23:55' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Public','2b972aa7-04f9-4db9-90da-e0a878a5fc7a',0,'2010-12-28T14:24:40' )
-- for Internal - Common - User
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','3d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',0,'2010-12-28T14:36:39' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','3ac712c6-1ee3-4053-99b5-37949b3054c3',0,'2010-12-28T14:35:15' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','3964ec47-2ac7-4c33-9660-523fa9ebb799',0,'2010-12-28T14:25:43' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','3c84bf0a-b4f9-4059-99c0-669f649efefe',0,'2010-12-28T14:23:55' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','3b972aa7-04f9-4db9-90da-e0a878a5fc7a',0,'2010-12-28T14:24:40' )
-- for Internal - Common - Admin
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','4d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',0,'2010-12-28T14:36:39' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','4ac712c6-1ee3-4053-99b5-37949b3054c3',0,'2010-12-28T14:35:15' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','4964ec47-2ac7-4c33-9660-523fa9ebb799',0,'2010-12-28T14:25:43' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','4c84bf0a-b4f9-4059-99c0-669f649efefe',0,'2010-12-28T14:23:55' )
INSERT INTO [User] ([AccountContextCode] ,[UserGuid] ,[IsAnonymous] ,[LastActivityDate] ) VALUES ( 'Internal','4b972aa7-04f9-4db9-90da-e0a878a5fc7a',0,'2010-12-28T14:24:40' )

-- RegisterUser
-- for Public - Common - User
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','1d3b4e7c-bf7b-4a9d-ba0b-134676c4c813','user5','user5','','','user5',0,'s','','user5@email.com','user5@email.com','user5','user5',1,0,'2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39',0,'2010-12-28T14:36:39',0,'2010-12-28T14:36:39','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','1ac712c6-1ee3-4053-99b5-37949b3054c3','user4','user4','','','user4',0,'s','','user4@email.com','user4@email.com','user4','user4',1,0,'2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15',0,'2010-12-28T14:35:15',0,'2010-12-28T14:35:15','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','1964ec47-2ac7-4c33-9660-523fa9ebb799','user3','user3','','','user3',0,'s','','user3@email.com','user3@email.com','user3','user3',1,0,'2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43',0,'2010-12-28T14:25:43',0,'2010-12-28T14:25:43','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','1c84bf0a-b4f9-4059-99c0-669f649efefe','user1','user1','','','user1',0,'s','','user1@email.com','user1@email.com','user1','user1',1,0,'2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55',0,'2010-12-28T14:23:55',0,'2010-12-28T14:23:55','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','1b972aa7-04f9-4db9-90da-e0a878a5fc7a','user2','user2','','','user2',0,'s','','user2@email.com','user2@email.com','user2','user2',1,0,'2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40',0,'2010-12-28T14:24:40',0,'2010-12-28T14:24:40','' )
-- for Public - Common - Admin
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','2d3b4e7c-bf7b-4a9d-ba0b-134676c4c813','admin5','admin5','','','admin5',0,'s','','admin5@email.com','admin5@email.com','admin5','admin5',1,0,'2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39',0,'2010-12-28T14:36:39',0,'2010-12-28T14:36:39','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','2ac712c6-1ee3-4053-99b5-37949b3054c3','admin4','admin4','','','admin4',0,'s','','admin4@email.com','admin4@email.com','admin4','admin4',1,0,'2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15',0,'2010-12-28T14:35:15',0,'2010-12-28T14:35:15','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','2964ec47-2ac7-4c33-9660-523fa9ebb799','admin3','admin3','','','admin3',0,'s','','admin3@email.com','admin3@email.com','admin3','admin3',1,0,'2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43',0,'2010-12-28T14:25:43',0,'2010-12-28T14:25:43','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','2c84bf0a-b4f9-4059-99c0-669f649efefe','admin1','admin1','','','admin1',0,'s','','admin1@email.com','admin1@email.com','admin1','admin1',1,0,'2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55',0,'2010-12-28T14:23:55',0,'2010-12-28T14:23:55','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Public','2b972aa7-04f9-4db9-90da-e0a878a5fc7a','admin2','admin2','','','admin2',0,'s','','admin2@email.com','admin2@email.com','admin2','admin2',1,0,'2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40',0,'2010-12-28T14:24:40',0,'2010-12-28T14:24:40','' )
-- for Internal - Common - User
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','3d3b4e7c-bf7b-4a9d-ba0b-134676c4c813','user5','user5','','','user5',0,'s','','user5@email.com','user5@email.com','user5','user5',1,0,'2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39',0,'2010-12-28T14:36:39',0,'2010-12-28T14:36:39','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','3ac712c6-1ee3-4053-99b5-37949b3054c3','user4','user4','','','user4',0,'s','','user4@email.com','user4@email.com','user4','user4',1,0,'2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15',0,'2010-12-28T14:35:15',0,'2010-12-28T14:35:15','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','3964ec47-2ac7-4c33-9660-523fa9ebb799','user3','user3','','','user3',0,'s','','user3@email.com','user3@email.com','user3','user3',1,0,'2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43',0,'2010-12-28T14:25:43',0,'2010-12-28T14:25:43','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','3c84bf0a-b4f9-4059-99c0-669f649efefe','user1','user1','','','user1',0,'s','','user1@email.com','user1@email.com','user1','user1',1,0,'2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55',0,'2010-12-28T14:23:55',0,'2010-12-28T14:23:55','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','3b972aa7-04f9-4db9-90da-e0a878a5fc7a','user2','user2','','','user2',0,'s','','user2@email.com','user2@email.com','user2','user2',1,0,'2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40',0,'2010-12-28T14:24:40',0,'2010-12-28T14:24:40','' )
-- for Internal - Common - Admin
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','4d3b4e7c-bf7b-4a9d-ba0b-134676c4c813','admin5','admin5','','','admin5',0,'s','','admin5@email.com','admin5@email.com','admin5','admin5',1,0,'2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39','2010-12-28T14:36:39',0,'2010-12-28T14:36:39',0,'2010-12-28T14:36:39','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','4ac712c6-1ee3-4053-99b5-37949b3054c3','admin4','admin4','','','admin4',0,'s','','admin4@email.com','admin4@email.com','admin4','admin4',1,0,'2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15','2010-12-28T14:35:15',0,'2010-12-28T14:35:15',0,'2010-12-28T14:35:15','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','4964ec47-2ac7-4c33-9660-523fa9ebb799','admin3','admin3','','','admin3',0,'s','','admin3@email.com','admin3@email.com','admin3','admin3',1,0,'2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43','2010-12-28T14:25:43',0,'2010-12-28T14:25:43',0,'2010-12-28T14:25:43','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','4c84bf0a-b4f9-4059-99c0-669f649efefe','admin1','admin1','','','admin1',0,'s','','admin1@email.com','admin1@email.com','admin1','admin1',1,0,'2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55','2010-12-28T14:23:55',0,'2010-12-28T14:23:55',0,'2010-12-28T14:23:55','' )
INSERT INTO [RegisterUser] ([AccountContextCode] ,[UserGuid] ,[UserName] ,[LoweredUserName] ,[NameAlias] ,[MobileAlias] ,[Password] ,[PasswordFormat] ,[PasswordSalt] ,[MobilePin] ,[Email] ,[LoweredEmail] ,[PasswordQuestion] ,[PasswordAnswer] ,[IsApproved] ,[IsLockedOut] ,[CreateDate] ,[LastLoginDate] ,[LastPasswordChangedDate] ,[LastLockedOutDate] ,[FailedPasswordAttemptCount] ,[FailedPasswordAttemptWindowStart] ,[FailedPasswordAnswerAttemptCount] ,[FailedPasswordAnswerAttemptWindowStart] ,[Comment] ) VALUES ( 'Internal','4b972aa7-04f9-4db9-90da-e0a878a5fc7a','admin2','admin2','','','admin2',0,'s','','admin2@email.com','admin2@email.com','admin2','admin2',1,0,'2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40','2010-12-28T14:24:40',0,'2010-12-28T14:24:40',0,'2010-12-28T14:24:40','' )

--Insert Script for 5 rows of table: UsersInRoles
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '1d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',1 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '1ac712c6-1ee3-4053-99b5-37949b3054c3',1 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '1964ec47-2ac7-4c33-9660-523fa9ebb799',1 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '1c84bf0a-b4f9-4059-99c0-669f649efefe',1 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '1b972aa7-04f9-4db9-90da-e0a878a5fc7a',1 )

INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '2d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',2 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '2ac712c6-1ee3-4053-99b5-37949b3054c3',2 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '2964ec47-2ac7-4c33-9660-523fa9ebb799',2 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '2c84bf0a-b4f9-4059-99c0-669f649efefe',2 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '2b972aa7-04f9-4db9-90da-e0a878a5fc7a',2 )

INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '3d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',3 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '3ac712c6-1ee3-4053-99b5-37949b3054c3',3 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '3964ec47-2ac7-4c33-9660-523fa9ebb799',3 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '3c84bf0a-b4f9-4059-99c0-669f649efefe',3 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '3b972aa7-04f9-4db9-90da-e0a878a5fc7a',3 )

INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '4d3b4e7c-bf7b-4a9d-ba0b-134676c4c813',4 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '4ac712c6-1ee3-4053-99b5-37949b3054c3',4 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '4964ec47-2ac7-4c33-9660-523fa9ebb799',4 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '4c84bf0a-b4f9-4059-99c0-669f649efefe',4 )
INSERT INTO [UsersInRoles] ([UserGuid] ,[RoleId] ) VALUES ( '4b972aa7-04f9-4db9-90da-e0a878a5fc7a',4 )

GO






GO
